package cn.lanqiao.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import cn.lanqiao.dao.WordDao;
import cn.lanqiao.entity.ForgetWord;
import cn.lanqiao.entity.RememberWord;
import cn.lanqiao.entity.Word;
import cn.lanqiao.util.JdbcUtil;

public class WordDaoImpl implements WordDao {

	@Override
	//保存记住的单词到数据库
	public void saveRememberWord(RememberWord rw) {
		int wordId = rw.getWordId();
		String word = rw.getWord();
		String translation = rw.getTranslation();
		String account = rw.getAccount();
		Connection conn = JdbcUtil.getConnection();
		String sql = "insert into userrememberword values(?,?,?,?)";
		PreparedStatement ps = null;
		try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1, wordId);
			ps.setString(2, word);
			ps.setString(3, translation);
			ps.setString(4, account);
			ps.executeUpdate();
			
			JdbcUtil.close(null, ps, conn);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	@Override
	public int getWordId(String word) {
		int wordId = -1;
		Connection conn = JdbcUtil.getConnection();
		String sql = "select wordid from ect4word where word=?";
		try {
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setString(1, word);
			ResultSet  rs = ps.executeQuery();
			if(rs.next()) {
				wordId = rs.getInt("wordid");
			}
			JdbcUtil.close(rs, ps, conn);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return wordId;
	}

	@Override
	public void saveForgetWord(ForgetWord fw) {
		int wordId = fw.getWordId();
		String word = fw.getWord();
		String translation = fw.getTranslation();
		String account = fw.getAccount();
		Connection conn = JdbcUtil.getConnection();
		String sql = "insert into userforgetword values(?,?,?,?)";
		try {
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setInt(1, wordId);
			ps.setString(2, word);
			ps.setString(3, translation);
			ps.setString(4, account);
			ps.executeUpdate();
			JdbcUtil.close(null, ps, conn);
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	@Override
	public RememberWord getRememberWord(String uaccount) {
		RememberWord rw = new RememberWord();
		Connection conn = JdbcUtil.getConnection();
		String sql = "select * from (select * from userrememberword order by dbms_random.value)where rownum <=1 and account=?";
		try {
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setString(1, uaccount);
			ResultSet rs = ps.executeQuery();
			if(rs.next()) {
				int wordId = rs.getInt("wordid");
				String word = rs.getString("word");
				String translation = rs.getString("translation");
				String account = rs.getString("account");
				rw.setWordId(wordId);
				rw.setWord(word);
				rw.setTranslation(translation);
				rw.setAccount(account);
				JdbcUtil.close(rs, ps, conn);
			}
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return rw;
	}

	@Override
	public ForgetWord getForgetWord(String uaccount) {
		ForgetWord fw = new ForgetWord();
		Connection conn = JdbcUtil.getConnection();
		String sql = "select * from (select * from userforgetword order by dbms_random.value)where rownum <=1 and account=?";
		try {
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setString(1, uaccount);
			ResultSet rs = ps.executeQuery();
			if(rs.next()) {
				int wordId = rs.getInt("wordid");
				String word = rs.getString("word");
				String translation = rs.getString("translation");
				String account = rs.getString("account");
				fw.setWordId(wordId);
				fw.setWord(word);
				fw.setTranslation(translation);
				fw.setAccount(account);
				JdbcUtil.close(rs, ps, conn);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return fw;
	}

	@Override
	public Word getWord() {
		Word w = new Word();
		Connection conn = JdbcUtil.getConnection();
		String sql = "select * from (select * from ect4word order by dbms_random.value)where rownum <=1";
		try {
			PreparedStatement ps = conn.prepareStatement(sql);
			ResultSet rs = ps.executeQuery();
			if(rs.next()) {
				int wordId = rs.getInt("wordId");
				String word = rs.getString("word");
				String translation = rs.getString("translation");
				w.setWordId(wordId);
				w.setWord(word);
				w.setTranslation(translation);
				JdbcUtil.close(rs, ps, conn);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return w;
	}

	@Override
	public void deleteWord(String account) {
		Connection conn = JdbcUtil.getConnection();
		String sql1 = "delete from userrememberword where account=?";
		String sql2 = "delete from userforgetword where account=?";
		PreparedStatement ps1 = null;
		PreparedStatement ps2 = null;
		try {
			conn.setAutoCommit(false);
			ps1 = conn.prepareStatement(sql1);
			ps1.setString(1, account);
			ps1.executeUpdate();
			ps2 = conn.prepareStatement(sql2);
			ps2.setString(1, account);
			ps2.executeUpdate();
			conn.commit();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JdbcUtil.close(null, ps1, null);
			JdbcUtil.close(null, ps2, conn);
		}
		
	}

	

}
